-- 1.查看达梦数据库库名
select name,create_time from v$database;

-- 2.查看达梦数据库实例名及状态
select name,instance_name,start_time,status$ from v$instance;

-- 3.大小敏感(1.为大小写敏感,0.为大小写不敏感)
SELECT SF_GET_CASE_SENSITIVE_FLAG();
SELECT CASE_SENSITIVE();

-- 4.获取某个模式下的所有数据表
select table_name from dba_tables where owner = 'COST';

-- 5.修改自增
select concat(concat(concat(concat('alter table ', dt.owner, '.'), t1.table_name, ' modify '), ' ', t1.column_name), ' int NOT NULL;'),
	t1.table_name,
	t1.column_name,
	t1.data_type,
	t1.data_length,
	t1.data_scale,
	t2.constraint_type 
from dba_tables dt
     left join user_tab_columns t1 on t1.table_name = dt.table_name
	 left join (select t1.column_name, t1.table_name, t2.constraint_type from user_ind_columns t1 left join user_constraints t2 on t1.index_name= t2.index_name ) t2 on t1.table_name= t2.table_name 
	      and t1.column_name= t2.column_name
where dt.owner = 'COST'
  and lower(t1.column_name) = 'id';
  
-- 6.递归节点和根节点
create view cost.vw_cost_item_sort
as
select ci.id, ci.code as cost_item_code, ci.name, ci.parent_code, connect_by_root(ci.code) as top_code
from cost.cost_item ci
where nvl(ci.delete_flag, 0) = 0
start with ci.parent_code = '0'
connect by ci.parent_code = prior ci.code; 

-- 7.查看表记录数
select t.owner TABLE_SCHEMA,TABLE_NAME, num_rows||'' TABLE_ROWS,
       to_char(round(s.BYTES /1024/1024.0,2),'fm99999999990.00') TABLE_VOLUME 
from dba_tables  t
     left join dba_segments s on t.table_name=s.segment_name
where s.segment_type like 'TABLE%'  and t.owner='COST'  
order by num_rows desc
